package com.turing.post.report.impl;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.Date;
import java.util.List;

import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.ejb.TransactionAttribute;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.report.util.IncomeDaySettlement;
import com.turing.post.report.util.IncomeDaySettlementDao;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

@Stateless(mappedName = "report.impl.IncomeDaySettlementDaoImpl")
@Remote
@Local
public class IncomeDaySettlementDaoImpl implements IncomeDaySettlementDao {
	/**
	 * 实体管理器
	 * 
	 * @see javax.persistence.EntityManager
	 */
	@PersistenceContext
	protected EntityManager _em;

	/**
	 * 创建商户、销售机构日收益汇总记录
	 * 
	 * @param ids
	 * @throws PCPSApplicationException
	 */
	public void insert(IncomeDaySettlement ids) throws PCPSApplicationException {
		GeneralDao.insert(_em, ids);
	}

	/**
	 * 修改商户、销售机构日收益汇总记录
	 * 
	 * @param ids
	 * @throws PCPSApplicationException
	 */
	public void update(IncomeDaySettlement ids) throws PCPSApplicationException {
		GeneralDao.update(_em, ids);
	}

	/**
	 * 删除某发卡商某日某商户所有的按销售机构日结数据
	 * 
	 * @param issueCompanyNo
	 *            发卡商号
	 * @param merchantNo
	 *            商户号
	 * 
	 */
	@TransactionAttribute(javax.ejb.TransactionAttributeType.REQUIRES_NEW)
	public void removeAllIncomeDaySettlementByMerchantNo(String issueCompanyNo,
			String merchantNo, Date tranDate) {
		Query query = _em
				.createNativeQuery("delete incomeDaySettlement t "
						+ "where t.issueCompanyNo = ?1 and t.merchantNo = ?2 and t.tranDate = ?3");
		query.setParameter(1, issueCompanyNo);
		query.setParameter(2, merchantNo);
		query.setParameter(3, tranDate);
		query.executeUpdate();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<IncomeDaySettlement> getIncomeDaySettlementByMerchantNoAndTrandate(
			String merchantNo, Date tranDate) {
		Query query = _em.createNativeQuery(
				"select * from incomeDaySettlement t "
						+ "where t.merchantNo = ?1 and t.tranDate = ?2",
				IncomeDaySettlement.class);
		query.setParameter(1, merchantNo);
		query.setParameter(2, tranDate);
		return query.getResultList();
	}

	/**
	 * 获取某商户某时间段的扎帐集合
	 * 
	 * @param merchantNo
	 * @param startDate
	 *            开始日期
	 * @param endDate
	 *            结束日期
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<IncomeDaySettlement> getIncomeDaySettlementByMerchantNoAndTrandate(
			String merchantNo, Date startdate, Date enddate) {
		Query query = _em
				.createNativeQuery(
						"select * from incomeDaySettlement t "
								+ "where t.merchantNo = ?1 and t.tranDate between ?2 and ?3",
						IncomeDaySettlement.class);
		query.setParameter(1, merchantNo);
		query.setParameter(2, startdate);
		query.setParameter(3, enddate);
		return query.getResultList();
	}

	@Override
	public BigDecimal getTotalAmountByMerchantNoAndTrandate(String merchantNo,
			Date tranDate) {
		Query query = _em.createNativeQuery(
				"select sum(Consume) from incomeDaySettlement t "
						+ "where t.merchantNo = ?1 and t.tranDate = ?2",
				IncomeDaySettlement.class);
		query.setParameter(1, merchantNo);
		query.setParameter(2, tranDate);
		return new BigDecimal(query.getSingleResult().toString());
	}

	/**
	 * 渠道收入统计表(内容)---报表
	 * 
	 * @param startDate
	 *            起始日期
	 * @param endDate
	 *            终止日期
	 * @param return
	 *            渠道收入统计表数组对象
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getIncomeDaySettlementTotalAll(PCPSWebInput webInput,
			String branchNoCond, String startDate, String endDate) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(branchNoCond)
				|| PCPSUtil.isEmpty(startDate) || PCPSUtil.isEmpty(endDate))
			return null;
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select t.salebranchno,t.merchantno,  sum(t.sincome+t.mincome) "
				+ " from IncomeDaySettlement t"
				+ " where t.issuecompanyno= ?1 "
				+ " and t.TranDate>=to_date(?2,'yyyy-MM-dd') "
				+ " and t.TranDate<=to_date(?3,'yyyy-MM-dd') "
				+ " and t.salebranchno in ("
				+ branchNoCond
				+ ")"
				+ " group by t.salebranchno,t.merchantno,t.mincomebranchid,t.sincomebranchid order by t.salebranchno,t.merchantno";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuecompanyno);
		query.setParameter(2, startDate);
		query.setParameter(3, endDate);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	/**
	 * 渠道收入汇总表(内容)---报表
	 * 
	 * @param startDate
	 *            起始日期
	 * @param endDate
	 *            终止日期
	 * @param return
	 *            渠道收入汇总表数组对象
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getIncomeDaySettlementCountAll(PCPSWebInput webInput,
			String branchNoCond, String startDate, String endDate) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(branchNoCond)
				|| PCPSUtil.isEmpty(startDate) || PCPSUtil.isEmpty(endDate))
			return null;
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select t.salebranchno,t.merchantno,t.mincomebranchid,t.sincomebranchid,sum(t.mincome) as mincome,sum(t.sincome) as sincome"
				+ " from IncomeDaySettlement t"
				+ " where t.issuecompanyno= ?1 "
				+ " and t.TranDate>=to_date(?2,'yyyy-MM-dd') "
				+ " and t.TranDate<=to_date(?3,'yyyy-MM-dd') "
				// + " and t.salebranchno in ("+branchNoCond+")"
				+ " group by t.salebranchno,t.merchantno,t.mincomebranchid,t.sincomebranchid order by t.salebranchno,t.merchantno";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuecompanyno);
		query.setParameter(2, startDate);
		query.setParameter(3, endDate);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	/**
	 * 渠道收入汇总表(表头)-----报表
	 * 
	 * @param startDate
	 *            起始日期
	 * @param endDate
	 *            终止日期
	 * @param return
	 *            渠道收入汇总表数组对象
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getIncomeDaySettlementTotalAllTitle(
			PCPSWebInput webInput, String branchNoCond, String startDate,
			String endDate) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(branchNoCond)
				|| PCPSUtil.isEmpty(startDate) || PCPSUtil.isEmpty(endDate))
			return null;
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select distinct(t.merchantno) "
				+ " from IncomeDaySettlement t"
				+ " where  t.issuecompanyno= ?1 "
				+ " and t.TranDate>=to_date(?2,'yyyy-mm-dd') "
				+ " and t.TranDate<=to_date(?3,'yyyy-mm-dd') "
				+ " and t.salebranchno in (" + branchNoCond + ") "
				+ " order by t.merchantno";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuecompanyno);
		query.setParameter(2, startDate);
		query.setParameter(3, endDate);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	/**
	 * 渠道收入统计表(内容)---报表
	 * 
	 * @param issueCompanyNo
	 *            发卡商编号
	 * @param saleBranchBno
	 *            销售机构号
	 * @param startDate
	 *            起始日期
	 * @param endDate
	 *            终止日期
	 * @param return
	 *            渠道收入汇总表数组对象
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getIncomeDaySettlementTotalBySaleBranchno(
			PCPSWebInput webInput, String branchNoCond, String startDate,
			String endDate) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(branchNoCond)
				|| PCPSUtil.isEmpty(startDate) || PCPSUtil.isEmpty(endDate))
			return null;
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select t.salebranchno,t.merchantno,  sum(t.sincome) "
				+ " from IncomeDaySettlement t"
				+ " where  t.issuecompanyno= ?1 "
				+ " and t.TranDate>=to_date(?2,'yyyy-mm-dd') "
				+ " and t.TranDate<=to_date(?3,'yyyy-mm-dd') "
				+ " and t.salebranchno in ("
				+ branchNoCond
				+ ") "
				+ " group by t.salebranchno,t.merchantno order by t.salebranchno,t.merchantno";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuecompanyno);
		query.setParameter(2, startDate);
		query.setParameter(3, endDate);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	/**
	 * 渠道收入统计表(表头)-----报表
	 * 
	 * @param issueCompanyNo
	 *            发卡商编号
	 * @param saleBranchBno
	 *            销售机构号
	 * @param startDate
	 *            起始日期
	 * @param endDate
	 *            终止日期
	 * @param return
	 *            渠道收入汇总表数组对象
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getIncomeDaySettlementTotalBySaleBranchnoTitle(
			PCPSWebInput webInput, String branchNoCond, String startDate,
			String endDate) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(branchNoCond)
				|| PCPSUtil.isEmpty(startDate) || PCPSUtil.isEmpty(endDate))
			return null;
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select distinct(t.merchantno),t.salebranchno "
				+ " from IncomeDaySettlement t"
				+ " where  t.issuecompanyno= ?1 "
				+ " and t.TranDate>=to_date(?2,'yyyy-mm-dd') "
				+ " and t.TranDate<=to_date(?3,'yyyy-mm-dd') "
				+ " and t.salebranchno in (" + branchNoCond + ") "
				+ " order by t.salebranchno,t.merchantno";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuecompanyno);
		query.setParameter(2, startDate);
		query.setParameter(3, endDate);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getEachUnitVolumeTransactionsSummary(
			PCPSWebInput webInput, String startDate, String endDate) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(startDate) || PCPSUtil.isEmpty(endDate))
			return null;
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select t.mincomebranchid , t.sincomebranchid,sum(t.consume) "
				+ " from IncomeDaySettlement t"
				+ " where  t.issuecompanyno= ?1 "
				+ " and t.TranDate>=to_date(?2,'yyyy-mm-dd') "
				+ " and t.TranDate<=to_date(?3,'yyyy-mm-dd') "
				+ " group by t.mincomebranchid, t.sincomebranchid "
				+ " order by t.mincomebranchid,t.sincomebranchid";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuecompanyno);
		query.setParameter(2, startDate);
		query.setParameter(3, endDate);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getIncomeDaySettlementStatistics(
			BigInteger incomeBranchID, String startDate, String endDate) {
		if (incomeBranchID == null || PCPSUtil.isEmpty(startDate)
				|| PCPSUtil.isEmpty(endDate))
			return null;
		String sql = "SELECT TO_CHAR(A.SORTDATE, 'YYYY-MM-DD'), SUM(C.INCOME), SUM(C.COST), SUM(C.SETTLEMENT) "
				+ "FROM "
				+ "(SELECT TO_DATE(?1, 'YYYY-MM-DD')+1-ROWNUM SORTDATE "
				+ "FROM DUAL CONNECT BY LEVEL <= CEIL(TO_DATE(?1, 'YYYY-MM-DD') - TO_DATE(?2, 'YYYY-MM-DD')+1)) A "
				+ "LEFT JOIN "
				+ "(SELECT B.TRANDATE TRANDATE, "
				+ "CASE WHEN B.MINCOMEBRANCHID = ?3 AND B.SINCOMEBRANCHID = ?3 THEN  B.SINCOME + B.MINCOME + B.SPASTINCOME + B.MPASTINCOME "
				+ "WHEN B.MINCOMEBRANCHID = ?3 THEN B.MINCOME  + B.MPASTINCOME "
				+ "WHEN B.SINCOMEBRANCHID = ?3 THEN B.SINCOME + B.SPASTINCOME END INCOME, "
				+ "0 COST, "
				+ "CASE WHEN B.MINCOMEBRANCHID = ?3 AND B.SINCOMEBRANCHID = ?3 THEN  B.SINCOME + B.MINCOME + B.SPASTINCOME + B.MPASTINCOME "
				+ "WHEN B.MINCOMEBRANCHID = ?3 THEN B.MINCOME  + B.MPASTINCOME "
				+ "WHEN B.SINCOMEBRANCHID = ?3 THEN B.SINCOME + B.SPASTINCOME END - 0 SETTLEMENT "
				+ "FROM INCOMEDAYSETTLEMENT B WHERE B.MINCOMEBRANCHID = ?3 OR B.SINCOMEBRANCHID = ?3) C "
				+ "ON A.SORTDATE = C.TRANDATE "
				+ "GROUP BY A.SORTDATE "
				+ "ORDER BY A.SORTDATE DESC";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, endDate);
		query.setParameter(2, startDate);
		query.setParameter(3, incomeBranchID);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getIncomeSettlementStatistics(
			BigInteger incomeBranchID, String startDate, String endDate) {
		String sql = "SELECT B.INCOMEBRANCHID, SUM(B.INCOME), SUM(B.COST), SUM(B.SETTLEMENT) FROM "
				+ "(SELECT CASE WHEN A.MINCOMEBRANCHID = ?1 AND A.SINCOMEBRANCHID = ?1 THEN  A.MINCOMEBRANCHID "
				+ "WHEN A.MINCOMEBRANCHID = ?1 THEN A.MINCOMEBRANCHID "
				+ "WHEN A.SINCOMEBRANCHID = ?1 THEN A.SINCOMEBRANCHID END INCOMEBRANCHID, "
				+ "CASE WHEN A.MINCOMEBRANCHID = ?1 AND A.SINCOMEBRANCHID = ?1 THEN  A.SINCOME + A.MINCOME + A.SPASTINCOME + A.MPASTINCOME "
				+ "WHEN A.MINCOMEBRANCHID = ?1 THEN A.MINCOME  + A.MPASTINCOME "
				+ "WHEN A.SINCOMEBRANCHID = ?1 THEN A.SINCOME + A.SPASTINCOME END INCOME, "
				+ "0 COST, "
				+ "CASE WHEN A.MINCOMEBRANCHID = ?1 AND A.SINCOMEBRANCHID = ?1 THEN  A.SINCOME + A.MINCOME + A.SPASTINCOME + A.MPASTINCOME "
				+ "WHEN A.MINCOMEBRANCHID = ?1 THEN A.MINCOME  + A.MPASTINCOME "
				+ "WHEN A.SINCOMEBRANCHID = ?1 THEN A.SINCOME + A.SPASTINCOME END - 0 SETTLEMENT "
				+ "FROM INCOMEDAYSETTLEMENT A "
				+ "WHERE (A.MINCOMEBRANCHID = ?1 OR A.SINCOMEBRANCHID = ?1) "
				+ "AND A.TRANDATE >= TO_DATE(?2, 'YYYY-MM-DD') "
				+ "AND A.TRANDATE <= TO_DATE(?3, 'YYYY-MM-DD')) B "
				+ "GROUP  BY B.INCOMEBRANCHID";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, incomeBranchID);
		query.setParameter(2, startDate);
		query.setParameter(3, endDate);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	@SuppressWarnings("unchecked")
	public List<Object[]> getIncomeSettlementConsumeData(String brchNo,
			String startDate, String endDate) {
		if (brchNo == null) {
			return null;
		}
		String sql = "select t.merchantno,nvl(sum(t.consume),0),nvl(sum(t.consumecount),0) from INCOMEDAYSETTLEMENT t"
				+ " where t.salebranchno in ("
				+ brchNo
				+ ")"
				+ " and t.trandate >= to_date(?1,'yyyy-MM-dd')"
				+ " and t.trandate <= to_date(?2,'yyyy-MM-dd')"
				+ " group by t.merchantno";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, startDate);
		query.setParameter(2, endDate);
		return query.getResultList();
	}
}